BEGIN;

DROP VIEW win_observation_textual_data_view;

CREATE OR REPLACE VIEW win_observation_textual_data_view AS
SELECT a.*, win_scientific_name.s_name, win_scientific_name.s_author from (
 SELECT win_observations.id, win_observations.s_kingdom, win_observations.s_phylum, win_observations.s_class, win_observations.s_order, win_observations.s_family, win_observations.s_genus, win_observations.s_species, win_observations.s_scientific_name, win_observations.waterbody_type, win_observations.waterbody_name, win_observations.comments, win_observations.record_language, win_observations.basis_of_record, win_observations.taxanomic_status, win_observations.nomenclature_type_status, win_observations.nativeness, win_observations.iucn_threat_category, win_observer.fname, win_observer.lname, win_observer.affiliation, win_observer.address, win_observer.email, win_observation_location.country, win_observation_location.region, win_observation_location.district, win_observation_location.city, win_observation_location.locality
   FROM win_observations, win_observer, win_observation_location
  WHERE win_observations.observer_id = win_observer.id AND win_observations.id = win_observation_location.observation_id
  ORDER BY win_observations.id) AS a
  left join win_scientific_name ON a.id = win_scientific_name.observation_id;

CREATE OR REPLACE VIEW win_observation_statistics_view AS
 SELECT ( SELECT count(drupal_users.uid) AS count
           FROM drupal_users
          WHERE drupal_users.uid > 1) AS num_contributors, ( SELECT count(DISTINCT drupal_profile_values.value) AS count
           FROM drupal_profile_values
          WHERE drupal_profile_values.fid::integer = (( SELECT drupal_profile_fields.fid
                   FROM drupal_profile_fields
                  WHERE drupal_profile_fields.name::text = 'profile_country'::text))) AS num_contributor_countries, ( SELECT count(win_observations.id) AS count
           FROM win_observations) AS num_contributions, ( SELECT count(DISTINCT win_validation.observation_id) AS count
           FROM win_validation) AS num_validated_contributions;

INSERT INTO win_iucn_threat_category (tc_name) VALUES ('Not Known');

COMMIT;